<?php
include_once 'template/header_index.php';
// 参考：http://www.php.net/manual/zh/pdo.connections.php
// https://segmentfault.com/a/1190000004106175

include_once 'data/wx_sign_db_config.php';
try {
// array(PDO::ATTR_PERSISTENT => true) 开启持久化连接
    $dbh = new PDO('mysql:host=' . $db['db_host'] . ';dbname=' . $db['db_name'], $db['user'], $db['pwd'], array(PDO::ATTR_PERSISTENT => true));
    //  页面编码的声明,否则遇到中文会出现乱码
    $dbh->query('set names utf8;');

    $sql_article = 'SELECT COUNT(*) count,b.`title`,b.`start_time` FROM sign_table a,class_subject b WHERE a.`class_id`=b.`id` GROUP BY a.class_id';
    $sql_user = 'SELECT COUNT(*) count,b.name,b.from_group FROM sign_table a,sign_user b WHERE a.`openid`=b.`openid` GROUP BY b.`openid` ORDER BY count DESC';

    // 现在运行完成，在此关闭连接

?>
<div class="col-sm-9 col-sm-offset-3 col-md-10 col-md-offset-2 main">
    <h2 class="sub-header">微信文章<span style="color: #c00;">签到数据</span></h2>
    <div class="row placeholders">
        <div class="table-responsive col-xs-6 col-sm-6 placeholder">
            <table class="table table-bordered  table-hover table-condensed">
                <thead><tr style='font-weight:bold;'><td>日期</td><td>文章标题</td><td>签到次数</td></tr></thead>
                <tbody>
                    <?php
                    $count = 0;
                    $times = 0;
                    foreach ($dbh->query($sql_article) as $row) {
//                        var_dump($row);
                        echo '<tr>';
                        echo '<td>' . substr($row['start_time'], 0, 10) . '</td>';
                        echo '<td>' . $row['title'] . '</td>';
                        echo '<td>' . $row['count'] . '</td>';
                        echo '</tr>';
                        $count += intval($row['count']);
                        $times += 1;
                    }
                    echo '<tr style="font-weight:bold;color:#0B8D9A;"><td>Total</td><td>' . $times . '</td><td>' . $count . '</td></tr>';
                    ?>
                </tbody>
            </table>
        </div>            
        <div class="table-responsive col-xs-6 col-sm-6 placeholder">
            <table class="table table-bordered  table-hover table-condensed">
                <thead><tr style='font-weight:bold;'><td>日期</td><td>文章标题</td><td>签到次数</td></tr></thead>
                <tbody>
                    <?php
//                    $count = 0;
//                    $times = 0;
                    foreach ($dbh->query($sql_user) as $res) {
//                        var_dump($row);
                        echo '<tr>';
                        echo '<td>' . $res['name'] . '</td>';
                        echo '<td>' . $res['count'] . '</td>';
                        echo '<td>' . $res['from_group'] . '</td>';
                        echo '</tr>';                        
//                        $count += intval($row['count']);
//                        $times += 1;
                    }
//                    echo '<tr style="font-weight:bold;color:#0B8D9A;"><td>Total</td><td>' . $times . '</td><td>' . $count . '</td></tr>';
                    ?>
                </tbody>
            </table>
        </div>            
    </div>
</div>
<?php
    $dbh = null;
} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
    die();
}
include_once 'template/footer.php';
?>